package zy.dao.sell.sell.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.sell.SellDAO;
import zy.entity.sell.cash.T_Sell_Shop;
import zy.entity.sell.cash.T_Sell_ShopList;
import zy.util.StringUtil;
@Repository
public class SellDAOImpl extends BaseDaoImpl implements SellDAO{

	@Override
	public Map<String,Object> countShop(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object em_code = param.get("em_code");
		Object code = param.get("code");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) count, ");
		sql.append(" SUM(sh_amount) sh_amount,SUM(sh_money) sh_money,SUM(sh_sell_money) sh_sell_money,SUM(sh_cash) sh_cash,");
		sql.append(" SUM(sh_cd_money) sh_cd_money,");
		sql.append(" SUM(sh_vc_money) sh_vc_money,SUM(sh_point_money) sh_point_money,SUM(sh_ec_money) sh_ec_money,");
		sql.append(" SUM(sh_sd_money) sh_sd_money,SUM(sh_bank_money) sh_bank_money,SUM(sh_mall_money) sh_mall_money,");
		sql.append(" SUM(sh_lost_money) sh_lost_money,SUM(sh_wx_money) sh_wx_money,SUM(sh_ali_money) sh_ali_money,");
		sql.append(" SUM(sh_red_money) sh_red_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE sh_source=1");
		if(!StringUtil.isEmpty(begindate)){
			sql.append(" AND sh_date BETWEEN :begindate AND :enddate");
		}
		if(!StringUtil.isEmpty(code)){
			sql.append(" AND sh_number=:code");
		}
		if(!StringUtil.isEmpty(em_code)){
			sql.append(" AND sh_em_code=:em_code");
		}
		sql.append(" AND sh_shop_code=:shop_code");
		sql.append(" AND companyid=:companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), param);
	}

	@Override
	public List<T_Sell_Shop> listShop(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object em_code = param.get("em_code");
		Object code = param.get("code");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append(" sh_id,sh_number,sh_date,sh_em_code,sh_state,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE em_code=sh_em_code AND e.companyid=t.companyid) em_name,");
		sql.append(" sh_amount,sh_money,sh_sell_money,sh_cash,");
		sql.append(" sh_cost_money,sh_upcost_money,sh_cd_money,");
		sql.append(" sh_vc_money,sh_point_money,sh_ec_money,");
		sql.append(" sh_sd_money,sh_bank_money,sh_mall_money,");
		sql.append(" sh_lost_money,sh_wx_money,sh_ali_money,");
		sql.append(" sh_red_money,sh_remark");

		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE sh_source=1");
		if(!StringUtil.isEmpty(begindate)){
			sql.append(" AND sh_date BETWEEN :begindate AND :enddate");
		}
		if(!StringUtil.isEmpty(code)){
			sql.append(" AND sh_number=:code");
		}
		if(!StringUtil.isEmpty(em_code)){
			sql.append(" AND sh_em_code=:em_code");
		}
		sql.append(" AND sh_shop_code=:shop_code");
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}

	@Override
	public Integer countShopList(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object em_code = param.get("em_code");
		Object code = param.get("code");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) ");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		if(!StringUtil.isEmpty(code)){
			sql.append(" AND pd_no=:code");
		}
		sql.append(" WHERE 1=1");
		if(!StringUtil.isEmpty(begindate)){
			sql.append(" AND shl_date BETWEEN :begindate AND :enddate");
		}
		if(!StringUtil.isEmpty(em_code)){
			sql.append(" AND (shl_main=:em_code OR shl_slave=:em_code)");
		}
		if(StringUtil.isNotEmpty(param.get("bd_code"))){
			sql.append(" AND pd_bd_code = :bd_code");
		}
		if(StringUtil.isNotEmpty(param.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		sql.append(" AND shl_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, 
				Integer.class);
	}

	@Override
	public List<T_Sell_ShopList> listShopList(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object em_code = param.get("em_code");
		Object code = param.get("code");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT shl_id,shl_number,shl_date,shl_pd_code,");
		sql.append(" pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT bd_name FROM t_base_brand b WHERE b.bd_code=p.pd_bd_code AND b.companyid=p.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT CONCAT(vm_cardcode,'/',vm_name) FROM t_vip_member v WHERE v.vm_code=t.shl_vip_code AND v.companyid=t.companyid LIMIT 1) AS vm_name,");
		sql.append(" shl_cr_code,shl_sz_code,shl_br_code,");
		sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=t.shl_cr_code AND c.companyid=t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size s WHERE s.sz_code=t.shl_sz_code AND s.companyid=t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra b WHERE b.br_code=t.shl_br_code AND b.companyid=t.companyid LIMIT 1) AS br_name,");
		/*sql.append(" (SELECT em_name FROM t_base_emp e WHERE e.em_code=t.shl_main AND e.companyid=t.companyid LIMIT 1) AS main_name,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE e.em_code=t.shl_slave AND e.companyid=t.companyid LIMIT 1) AS slave_name,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE e.em_code=t.shl_em_code AND e.companyid=t.companyid LIMIT 1) AS em_name,");*/
		sql.append(" shl_em_code,shl_slave,shl_main,shl_da_code,");
		sql.append(" shl_amount,shl_sell_price,shl_price,");
		sql.append(" shl_money,shl_state,shl_vip_code");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		if(!StringUtil.isEmpty(code)){
			sql.append(" AND pd_no=:code");
		}
		sql.append(" WHERE 1=1");
		if(!StringUtil.isEmpty(begindate)){
			sql.append(" AND shl_date BETWEEN :begindate AND :enddate");
		}
		if(!StringUtil.isEmpty(em_code)){
			sql.append(" AND (shl_main=:em_code OR shl_slave=:em_code)");
		}
		if(StringUtil.isNotEmpty(param.get("bd_code"))){
			sql.append(" AND pd_bd_code = :bd_code");
		}
		if(StringUtil.isNotEmpty(param.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code");
		}
		sql.append(" AND shl_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public T_Sell_Shop queryByID(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append(" sh_id,sh_number,sh_date,sh_em_code,sh_state,sh_vip_code,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE em_code=sh_em_code AND e.companyid=t.companyid) em_name,");
		sql.append(" sh_amount,sh_money,sh_sell_money,sh_cash,sh_cd_money,sh_ec_money,sh_vc_money,");
		sql.append(" sh_shop_code,companyid");

		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE sh_id=:sh_id");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(),param, 
				new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}

	@Override
	public List<T_Sell_ShopList> listSell(T_Sell_Shop sell) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT shl_id,shl_number,shl_date,shl_pd_code,");
		sql.append(" pd_no,pd_name,pd_unit,pd_sign_price AS shl_sign_price,");
		sql.append(" shl_cr_code,shl_sz_code,shl_br_code,");
		sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=t.shl_cr_code AND c.companyid=t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size s WHERE s.sz_code=t.shl_sz_code AND s.companyid=t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra b WHERE b.br_code=t.shl_br_code AND b.companyid=t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE e.em_code=t.shl_main AND e.companyid=t.companyid LIMIT 1) AS main_name,");
		sql.append(" shl_em_code,shl_slave,shl_main,shl_da_code,");
		sql.append(" shl_amount,shl_sell_price,shl_price,");
		sql.append(" shl_money,shl_state,shl_vip_code");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_number=:sh_number");
		sql.append(" AND shl_shop_code=:sh_shop_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), new BeanPropertySqlParameterSource(sell), 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}
	
}
